class Category < ActiveRecord::Base
  has_and_belongs_to_many :drugs, :order => 'name, id ASC'
  include SideffectHelper
  def self.with_prefix(prefix)
    find(:all, :conditions => ["name like ?", prefix + "%"], :order => "name ASC")
  end
  
  def drugs_ordered_by_ratings
    sql = <<-SQL
      SELECT d.*, count(r.id) cnt
      FROM categories_drugs cd, ratings r, drugs d
      WHERE cd.category_id = ?
      AND cd.drug_id = r.drug_id
      AND d.id = cd.drug_id
      GROUP BY d.id
      ORDER BY cnt DESC, d.name ASC
    SQL
    Drug.find_by_sql([sql, self[:id]])
    # SELECT d.*, t.cnt2 cnt
    # FROM drugs d,
    # (
    #   SELECT cd.drug_id id2, count(r.id) cnt2
    #   FROM categories_drugs cd, ratings r
    #   WHERE cd.category_id = ?
    #   AND cd.drug_id = r.drug_id
    #   GROUP BY cd.drug_id
    # ) t
    # WHERE d.id = t.id2
    # ORDER BY cnt DESC, d.name ASC
  end
  
  def ratings_number(filter_options = {})
    sql = <<-SQL
      SELECT count(distinct r.id)
      FROM ratings r, categories_drugs cd
      WHERE cd.category_id = ?
      AND cd.drug_id = r.drug_id
      AND #{filter_sql(filter_options)}
    SQL
    self.class.count_by_sql([sql, self[:id]])
  end

  def sideffects(filter_options = {})
    sql = <<-SQL
      SELECT s.*, count(distinct r.id) cnt
      FROM sideffects s, ratings r, ratings_sideffects rs, categories_drugs cd
      WHERE cd.category_id = ?
      AND r.drug_id = cd.drug_id
      AND r.id = rs.rating_id 
      AND s.id = rs.sideffect_id
      AND #{filter_sql(filter_options)}
      GROUP BY s.id
      ORDER BY cnt DESC, s.name ASC
    SQL
    Sideffect.find_by_sql([sql, self[:id]])
  end
end
